/**
 * 
 */
package com.dsbridge.bank.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resources;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import com.dsbridge.bank.model.Account;
import com.dsbridge.bank.model.Customer;
import com.dsbridge.bank.model.DBUtils;

/**
 * @author Naveen
 * 
 */
@Repository
public class AccountDAO {

	/**
	 * This method will be used to retrieve the accounts by cutomerId.
	 * 
	 * @param customerid
	 * @return list of account objects
	 * @throws SQLException
	 */

	@Autowired
	private DataSource dataSource;;
	private JdbcTemplate insert;

	public int insert(final Account a) throws Exception {

		final String sql = "INSERT INTO ACCOUNT(ACCOUNT_NAME,CUST_ID) VALUES(?,?)";
		this.insert = new JdbcTemplate(dataSource);
		KeyHolder keyHolder = new GeneratedKeyHolder();

		insert.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql,
						new String[] { "ACCOUNT_NUM" });
				ps.setString(1, a.getAccountname());
				ps.setInt(2, a.getCustid());
				return ps;
			}
		}, keyHolder);

		return keyHolder.getKey().intValue();
	}

	public int update(Account a) throws SQLException {

		this.insert = new JdbcTemplate(dataSource);
		String sql = "UPDATE ACCOUNT SET CUST_ID = ? WHERE ACCOUNT_NUM = ? ";

		return insert.update(sql,
				new Object[] { a.getCustid(), a.getAccountnum() });
		
	}

	public Account delete(Account a) throws SQLException {

		
		Account a1;
		this.insert = new JdbcTemplate(dataSource);
		RowMapper<Account> mapper = new RowMapper<Account>() {

			public Account mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Account actor = new Account();
				actor.setAccountname(rs.getString("account_name"));
				actor.setAccountnum(rs.getInt("account_num"));
				actor.setCustid(rs.getInt("cust_id"));
				return actor;
			}
		};

		// notice the cast, the wrapping up of the 'id' argument
		// in an array, and the boxing of the 'id' argument as a reference type
		a1 = (Account) insert.queryForObject(
				"select * from Account where CUST_ID = ?", mapper,
				new Object[] { a.getCustid() });
		insert.update("DELETE FROM ACCOUNT WHERE CUST_ID = ? ",
				new Object[] {a.getCustid() });

		return a1;


	}

	public List<Account> retrieveAccbyCustId(int customerid)
			throws SQLException {
		List<Account> a = new ArrayList<Account>();

		PreparedStatement ps = null;
		String sql = "SELECT * FROM ACCOUNT WHERE CUST_ID = ?";
		Connection con = DBUtils.getConnection();

		if (con != null) {
			ps = con.prepareStatement(sql);
			// populate the data
			ps.setInt(1, customerid);
			// Execute the statements by passing the customerid
			ResultSet result = ps.executeQuery();

			while (result.next()) {
				Account a1 = new Account();

				a1.setAccountname(result.getString("ACCOUNT_NAME"));
				a1.setAccountnum(result.getInt("ACCOUNT_NUM"));
				a1.setCustid(result.getInt("CUST_ID"));

				a.add(a1);

			}
		} else {
			System.out.println("Unable to get the connection");
		}
		// close the statements and connections
		ps.close();
		DBUtils.closeConnection();

		return a;
	}

}
